<?php
/*
	sqlbackup
	---------
	$Author: hainam $
	$Id: sqlbackup.php 14 2010-11-17 15:27:37Z hainam $
	Lastest version: http://code.google.com/p/jtools/source/browse/trunk/sqlbackup/sqlbackup.php
	Author's homepage: http://www.jcisio.com (email: jcisio@gmail.com)
	
	FEATURE
	- Fast backup/restore, small files (when using gzip)
	- Support huge database and backup files
	- Support prefix (for multi-scripts share a database)
	- Support excluded tables (eg. you don't want back up a 'session table')
	- Friendly/Easy interface

	SPECIFICATION
	- Filename format vnt_dbname_date-or-anything.sql(.gz)
	- Everyline, who doesn't start by #, is a query
	- Consume little memory in backup mode (about 1MB when using gzip)
	- Consume much memory in restore mode, except for BIGDATA mode (about 1MB)
	- Require MySQL 3.23+, PHP 4.1.0+

	NOTES
	- Hasn't been tested carefully, use it as your own risk
	- set PARSESQL to 'true' to restore from others scripts' files (not recommended)
	- for a big database, please set BIGDATA to true
	- to restore into another db, change dbname in the filename (not in the configuration)

	HISTORY
    see at the end of the file
*/

require 'sqlbackup.ini.php';

define('VERSION', '$Date: 2010-11-17 15:27:37 +0000 (Wed, 17 Nov 2010) $');
function fixautoinc($table, $property)
{
	global $tabledump, $cond;
	
	$cond = '';
	if (ereg('auto_increment', $property))
	{
		$field = preg_replace('#^.*`([a-z_]+)`[^`]+? auto_increment.*$#s', '\1', $property);
		if ($rows = mysql_query("SELECT * FROM `$table` WHERE $field=0"))
		if (mysql_num_rows($rows)>0)
		{
			$numfields = mysql_num_fields($rows);
			$row = mysql_fetch_array($rows);
			$data = '(';
			for ($i=0; $i<$numfields; $i++)
			{
				if ($i!=0) $data .= ',';
				$data .= isset($row[$i]) ? "'".mysql_escape_string($row[$i])."'" : 'NULL';
			}
			$tabledump .= "INSERT INTO `$table` VALUES $data);\n";
			$tabledump .= "UPDATE `$table` SET `$field`=0 WHERE `$field`=1;\n";

			$cond = "WHERE `$field`>0";
		}
	}
}
function dumpstruct($table)
{
	global $tabledump;
	$tabledump = "\nDROP TABLE IF EXISTS `$table`;\n";
	$rows = mysql_query("SHOW CREATE TABLE `$table`");
	$data = mysql_fetch_array($rows);
	mysql_free_result($rows);
	$tabledump .= preg_replace('/\r|\n|\t/', '', $data[1]).";\n";
	return $data;
}

function do_dump_small($table, $fp=0)
{
	global $tabledump, $cond;
	$numrows = $table['Rows'];
	$avgsize = $table['Avg_row_length']+1;
	$table = $table['Name'];
	$perquery = intval(MAXLENGTH/$avgsize)+1;
	$parts = intval($numrows/$perquery)+1;

	if (in_array(substr($table, strlen($GLOBALS['prefix'])), $GLOBALS['exclude'])) return;
	
	$data = dumpstruct($table);
	fixautoinc($table, $data[1]);

	if ($fp) gzwrite($fp, $tabledump);
	else echo $tabledump;

	$z = 0;
	$part = 0;
	echo "($parts parts: ";
	while ($z<$numrows)
	{
		$rows = mysql_unbuffered_query("SELECT * FROM `$table` $cond LIMIT $z, $perquery");
		echo (++$part%10)? '.' : $part.' ';
		flush();
		$numfields = mysql_num_fields($rows);
		$dump = array();
		while ($row = mysql_fetch_row($rows))
		{
			$data = '(';
			for ($i=0; $i<$numfields; $i++)
			{
				if ($i!=0) $data .= ',';
				$data .= isset($row[$i]) ? "'".mysql_escape_string($row[$i])."'" : 'NULL';
			}
			$dump[] = $data.')';
			unset($row, $data);
		}
		mysql_free_result($rows);
		$z += $perquery;

		if ($fp)
			gzwrite($fp, "INSERT INTO `$table` VALUES " . implode(', ', $dump).";\n");
		else
			echo "INSERT INTO `$table` VALUES " . implode(', ', $dump).";\n";
		unset($dump);
	}
	echo ")";
}
function do_dump_big($table, $fp=0)
{
	global $tabledump, $cond;
	$table = $table['Name'];
	if (in_array(substr($table, strlen($GLOBALS['prefix'])), $GLOBALS['exclude'])) return;

	$data = dumpstruct($table);
	fixautoinc($table, $data[1]);

	if ($fp) $Fwrite($fp, $tabledump);
	else echo $tabledump;

	$rows = mysql_query("SELECT * FROM `$table` $cond");
	$numfields = mysql_num_fields($rows);
	$dump = array();
	$length = 0;
	while ($row = mysql_fetch_array($rows))
	{
		$data = '(';
		for ($i=0; $i<$numfields; $i++)
		{
			if ($i!=0) $data .= ',';
			$data .= isset($row[$i]) ? "'".mysql_escape_string($row[$i])."'" : 'NULL';
		}
		$dump[] = $data.')';
		$length += strlen($data)+1;

		if ($length>MAXLENGTH)
		{
			$tabledump = "INSERT INTO `$table` VALUES " . implode(', ', $dump).";\n";
			$dump = array();
			$length = 0;
			if ($fp)
				gzwrite($fp, $tabledump);
			else
				echo $tabledump;
		}
	}
	mysql_free_result($rows);

	if ($length>0)
	{
		$tabledump = "INSERT INTO `$table` VALUES " . implode(', ', $dump).";\n";
		if ($fp)
			gzwrite($fp, $tabledump);
		else
			echo $tabledump;
	}
}

function do_restore()
{
	if (empty($_GET['restore']))
	{
		echo 'Current backups';
		$dp = opendir($GLOBALS['path']);
		$theme = '<li><a href="'.$_SERVER['PHP_SELF'].'?restore=%s">%s</a> (%.2f MB)</li>';
		while($entryname = readdir($dp))
		{
			if(substr($entryname,0,4)=='vnt_') {
			  $dbname = preg_replace('/vnt_(.*)_(..)(..)(..)_(..)(..)\..*/','[\1] \4/\3/20\2 \5:\6', $entryname);
        printf($theme, substr($entryname,4), $dbname, filesize($GLOBALS['path'].'/'.$entryname)/1024/1024);
			}
		}
		closedir($dp);
	}
	else
	{
		$filename = $GLOBALS['path'].'/vnt_'.$_GET['restore'];
		if (!file_exists($filename)) die("$filename File not found!");
		if (BIGDATA)
		{
			$fp = @gzopen($filename, 'rb');
		}
		elseif (substr($filename, -4)=='.sql')
		{
			$sql_query = @fread(@fopen($filename, 'rb'), @filesize($filename));
		}
		else
		{
			$sql_query = @gzread(@gzopen($filename, 'rb'), MAXGZIP);
		}
		$database = preg_replace('/^(.*?)_\d\d.*$/', '\1', $_GET['restore']);
		mysql_select_db($database);
		echo "Selected database: $database<br>";

		if ($GLOBALS['clear_db']) {
		  $tables = list_tables($GLOBALS['prefix']);
		  mysql_query('DROP TABLE `'. implode('`,`', $tables) .'`');
		}
		
		if (BIGDATA)
		{
			$i = 0;
			echo "BIGDATA mode. It will be slower, but safer.<br>";
			echo "Running (each dot is a block of 100 queries): ";
			while (!gzeof($fp))
			{
				$tmp = trim(gzgets($fp));
				$i++;
				if (substr($tmp,0,1)!='#' && !empty($tmp))
					if (!mysql_query($tmp))
					{
						echo "<div style='font:10px Tahoma;color:red'>".htmlentities($tmp)."</div>";
						echo mysql_error();
						break;
					}
				if ($i%100==0) {echo '. ';flush();}
			}
		}
		else
		{
			if (PARSESQL)
			{
				$sql_query = remove_remarks($sql_query);
				$sql_query = split_sql_file($sql_query, ';');
			}
			else
				$sql_query = explode("\n", $sql_query);
		
			$n = count($sql_query);$j=0;
			echo "Running (".ceil($n/100)." blocks of queries): ";
			for ($i=0; $i<$n; $i++)
			{
				$tmp = trim($sql_query[$i]);
				if (substr($tmp,0,1)!='#' && !empty($tmp))
					if (!mysql_query($tmp))
					{
						echo "<div style='font:10px Tahoma;color:red'>".htmlentities($tmp)."</div>";
						echo mysql_error();
						break;
					}
				if ($i%100==0) {echo ++$j%10;flush();}
			}
		}
	}
}

function do_backup()
{
	global $gzipped, $path;
	if ($gzipped==='one')	
	{
		$filename = $path.'/vnt_'.$GLOBALS['database'].date('_ymd_Hi').'.sql.gz';
		$fp = @gzopen($filename, 'w');
	}
	else 
	{
		$filename = ($gzipped===true) ? tempnam($GLOBALS['tpath'], 'sqlbackup') : $filename = $path.'/vnt_'.$GLOBALS['database'].date('_ymd_Hi').'.sql';
		$fp = @fopen($filename, 'w');
	}	
	$fp or die($php_errormsg.'Open file error: '.$filename);
	

	// Header
	$header = "#----------------------------------------\n";
	$header.= "# DATABASE:  ".$GLOBALS['database']."\n";
	$header.= "# Date/Time:  ".date ("l dS of F Y H:i:s")."\n";
	$header.= "#----------------------------------------\n";
	$header.= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n";
	gzwrite($fp, $header);

	$tablesbackup = mysql_query("SHOW TABLE STATUS LIKE '".$GLOBALS['prefix']."%'");
	$rows = array();
	while ($row = mysql_fetch_array($tablesbackup)) $rows[] = $row;
	mysql_free_result($tablesbackup);
	$nums = count($rows);
	echo "Dumping ($nums tables): ";flush();$i=0;
	$dump_func = $GLOBALS['small_memory'] ? 'do_dump_small' : 'do_dump_big';
	for ($i=0; $i<$nums; $i++)
	{
		echo "<br>".($i+1)." ".$rows[$i][0]." ";flush();
		$dump_func($rows[$i], $fp);
	}
	if ($gzipped==='one') gzclose($fp);
	else fclose($fp);

	if ($gzipped===true)
		echo "<br>Preparing to gzip...<script>setTimeout(\"location.href='$_SERVER[PHP_SELF]?gzip=".urlencode($filename)."'\", 2000)</script>";
	else
		echo '<br><a href="'.$_SERVER['PHP_SELF'].'?restore">Go to RESTORE section</a>';
}

function do_gzip()
{
	$filename = urldecode($_GET['gzip']);
	if (!file_exists($filename)) die('File not found');
	$nums = ceil(@filesize($filename)/1048576);
	echo "<br>Gzipping ($nums MB): ";flush();$i=0;
	$fr = @fopen ($filename, 'rb') or die($php_errormsg.'Read file error: '.$filename);
	if (DIRECT_DL) $GLOBALS['path']=$GLOBALS['tpath'];
	$newfile = $GLOBALS['path'].'/vnt_'.$GLOBALS['database'].date('_ymd_Hi').'.sql.gz';
	$zw = @gzopen ($newfile, 'wb') or die($php_errormsg.'Write file error: '.$newfile);
	while (!feof($fr))
	{
		gzwrite ($zw, fread ($fr, 1048576));
		echo ++$i%10;flush();
	}
	fclose ($zw);
	fclose ($fr);
	@unlink($filename);
	if (DIRECT_DL) echo '<script>location="?download='.urlencode($newfile).'";</script>';
	echo '<br><a href="'.$_SERVER['PHP_SELF'].'?restore">Go to RESTORE section</a>';
}
function do_download()
{
	$path = $_GET['download'];
	$name = basename($path);

	header("Content-Type: application/octet-stream"); 
	header("Content-Length: ".filesize($path)); 
	header("Content-Disposition: attachment; filename=$name");
	header("Cache-Control: no-cache, must-revalidate");
	header("Pragma: no-cache");
	readfile($path);
	@unlink($path);
	die;
}
function do_phpinfo()
{
	phpinfo();
}

function do_connect() {
  global $server, $username, $password, $database, $connect;
  $connect = @mysql_connect($server,$username,$password);
  if (! $connect) die("khong ket noi duoc voi mysql");
  mysql_select_db($database);
  //mysql_query('SET CHARACTER SET utf8');
  mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");
}

if (!(DIRECT_DL && isset($_GET['download'])))
{
	echo '<div style="font:9pt Verdana">Please wait until you see <b>Completed!</b> below<hr><br><br>';
}
flush();
$starttime = getmicrotime();

@set_time_limit(TIMEOUT);
@error_reporting (E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
function myErrorHandler ($errno, $errstr, $errfile, $errline) 
{
	die("<blockquote><hr>ERROR $errno<br>REASON: $errstr<br>FILE: $errfile<br>LINE: $errline<hr></blockquote>");
}
$old_error_handler = set_error_handler("myErrorHandler"); 

$path = (substr($target,0,1)=='/') ? $target : str_replace("\\", "/", dirname(__FILE__)) . '/'.$target;
if (TMP_DIR != '')
	$tpath = TMP_DIR;
elseif (getenv('TMP') != '')
	$tpath = str_replace('\\', '/', getenv('TMP'));
elseif (substr(PHP_OS,0,3)=='Win')
	$tpath = 'c:/windows/temp';
else
	$tpath = '/tmp';

do_connect();

$_GET[$_SERVER['QUERY_STRING']] = '';		// PHP 4.1.2 fix
if (isset($_GET['restore']))
	do_restore();
elseif (isset($_GET['backup']))
	do_backup();
elseif (isset($_GET['gzip']))
	do_gzip();
elseif (isset($_GET['download']))
	do_download();
elseif (isset($_GET['phpinfo']))
	do_phpinfo();
else
	echo '<br>Do you want to <a href="'.$_SERVER['PHP_SELF'].'?backup">backup</a> or <a href="'.$_SERVER['PHP_SELF'].'?restore">restore</a> ?';

mysql_close($connect);

$spendtime = getmicrotime()-$starttime;
printf('<br><br><br><hr><b>Completed!</b> %.4f sec &nbsp; &nbsp; [<a href="%s">index</a> <a href="%s?phpinfo=go&test">PHPinfo</a>]<br>', $spendtime, $_SERVER['PHP_SELF'], $_SERVER['PHP_SELF']);
printf(implode(' &nbsp; ', array('GZIP %s', 'PARSESQL %s', 'BIGDATA %s', 'MEMORY %s', 'VERSION %s')),
  $gzipped?'on':'off', 
  PARSESQL?'on':'off', 
  BIGDATA?'on':'off', 
  $small_memory?'small':'large', 
  VERSION);
// ------------------------------------
// These functions are from phpBB 2.0
function remove_comments(&$output)
{
	$lines = $output;
	$output = "";

	// try to keep mem. use down
	$linecount = count($lines);

	$in_comment = false;
	for($i = 0; $i < $linecount; $i++)
	{
		if( preg_match("/^\/\*/", preg_quote($lines[$i])) )
		{
			$in_comment = true;
		}

		if( !$in_comment )
		{
			$output .= $lines[$i] . "\n";
		}

		if( preg_match("/\*\/$/", preg_quote($lines[$i])) )
		{
			$in_comment = false;
		}
	}

	unset($lines);
	return $output;
}

function remove_remarks($sql)
{
	$lines = explode("\n", $sql);
	
	// try to keep mem. use down
	$sql = "";
	
	$linecount = count($lines);
	$output = "";

	for ($i = 0; $i < $linecount; $i++)
	{
		if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
		{
			if ($lines[$i][0] != REMARK)
			{
				$output .= $lines[$i] . "\n";
			}
			else
			{
				$output .= "\n";
			}
			// Trading a bit of speed for lower mem. use here.
			$lines[$i] = "";
		}
	}
	
	return $output;
}
function split_sql_file($sql, $delimiter)
{
	// Split up our string into "possible" SQL statements.
	$tokens = explode($delimiter, $sql);

	// try to save mem.
	$sql = "";
	$output = array();
	
	// we don't actually care about the matches preg gives us.
	$matches = array();
	
	// this is faster than calling count($oktens) every time thru the loop.
	$token_count = count($tokens);
	for ($i = 0; $i < $token_count; $i++)
	{
		// Don't wanna add an empty string as the last thing in the array.
		if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
		{
			// This is the total number of single quotes in the token.
			$total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
			// Counts single quotes that are preceded by an odd number of backslashes, 
			// which means they're escaped quotes.
			$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);
			
			$unescaped_quotes = $total_quotes - $escaped_quotes;
			
			// If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
			if (($unescaped_quotes % 2) == 0)
			{
				// It's a complete sql statement.
				$output[] = $tokens[$i];
				// save memory.
				$tokens[$i] = "";
			}
			else
			{
				// incomplete sql statement. keep adding tokens until we have a complete one.
				// $temp will hold what we have so far.
				$temp = $tokens[$i] . $delimiter;
				// save memory..
				$tokens[$i] = "";
				
				// Do we have a complete statement yet? 
				$complete_stmt = false;
				
				for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
				{
					// This is the total number of single quotes in the token.
					$total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
					// Counts single quotes that are preceded by an odd number of backslashes, 
					// which means they're escaped quotes.
					$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);
			
					$unescaped_quotes = $total_quotes - $escaped_quotes;
					
					if (($unescaped_quotes % 2) == 1)
					{
						// odd number of unescaped quotes. In combination with the previous incomplete
						// statement(s), we now have a complete statement. (2 odds always make an even)
						$output[] = $temp . $tokens[$j];

						// save memory.
						$tokens[$j] = "";
						$temp = "";
						
						// exit the loop.
						$complete_stmt = true;
						// make sure the outer loop continues at the right point.
						$i = $j;
					}
					else
					{
						// even number of unescaped quotes. We still don't have a complete statement. 
						// (1 odd and 1 even always make an odd)
						$temp .= $tokens[$j] . $delimiter;
						// save memory.
						$tokens[$j] = "";
					}
					
				} // for..
			} // else
		}
	}

	return $output;
}
function getmicrotime()
{
   list($usec, $sec) = explode(" ",microtime()); 
   return ((double)$usec + (double)$sec); 
}

function list_tables($prefix) {
  $tables = mysql_query("SHOW TABLE STATUS LIKE '{$prefix}%'");
  $rows = array();
  while ($row = mysql_fetch_array($tables)) {
    $rows[] = $row['Name'];
  }
  mysql_free_result($tables);
  return $rows;
}

/**
 * History
  1.0 by chuongbom
  1.1 (13/12/2003) by pfiev
    - backup/restore/gzip in one script
    - new query syntax from 3.23 to dump table's structure
    - support FULLTEXT property
    - add many records in a single query
    - friendly interface
    - many small improvements
  1.1.1 (24/12/2003)
    - split out dump/compression to be able to deal with bigger data on safe mode
  1.2 (10/02/2004)
    - PHP 4.1.2 (Yahoo! webhosting) bug fix: sqlbackup?backup doesn't create $_GET['backup']
    - tiny memory/cache use in mySQL (and a little bit slower)
    - error handler
  1.3 (02/03/2004)
    - no temporary files
    - small_memory support (somewhen mysqldump can't work, but sqlbackup works always!)
    - convergence of version 1.1.1 and 1.2
  1.4 (25/08/2004)
    - value "0" in auto_increment support
  1.5 (21/10/2004)
    - quote table name & fields
  1.9 (4/11/2004)
    - bugfix in dump small
    - rewrite all 'dump' code
  1.9b (20/1/2005)
    - option to direct download
  1.9c (18/3/2005)
    - change the indicator when backup
  1.9d (5/6/2005)
    - MySQL 4.1 compatible (in SHOW TABLE STATUS)
  2.0 (2/10/2005)
    - One step gzip-backup (for safe-mode=on without uid enable)
  2.0b (15/7/2008)
    - Add time zone support (since PHP 5.1.0)
  2.0c (17/11/2010)
    - Add SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; (since MySQL 4.01.01)
 */
